namespace CISLib
{
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class ManageCustomerTrain
    {
        public static SqlDataReader GetCustomerTrain()
        {
            string strSQL = "Select * from TB_TrainDef order by TrainName ";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerTrain GetCustomerTrainViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            CustomerTrain customerTrain = new CustomerTrain();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_TrainDef where TrainId = @TrainId";
            MyDB.AddParameters(id, "TrainId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerTrain.TrainId = MyReader.GetGuid(0);
                customerTrain.TrainName = MyReader.GetString(1);
                customerTrain.TrainEnName = MyReader.GetString(2);
            }
            MyDB.DBClose();
            return customerTrain;
        }

        public static void InsertNewTrain(string name, string nameEn)
        {
            Guid id = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "TrainName", SqlDbType.VarChar);
            MyDB.AddParameters(nameEn, "TrainEnName", SqlDbType.VarChar);
            MyDB.AddParameters(id, "TrainId", SqlDbType.UniqueIdentifier);
            string strSQL = "Insert into TB_TrainDef (TrainName,TrainEnName,TrainId) values (@TrainName,@TrainEnName,@TrainId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static bool IsExistDefInOtherTable(Guid defId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(defId, "CustomerDefId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerBasicInfo where CustomerDefId = @CustomerDefId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
            }
            else
            {
                MyDB.DBClose();
                return true;
            }
            return false;
        }

        public static bool IsExistSameTrainName(string name)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "TrainName", SqlDbType.VarChar);
            string strSQL = "select * from TB_TrainDef where TrainName = @TrainName";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static void RemoveTrainViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "TrainId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_TrainDef where TrainId = @TrainId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateTrain(string name, string nameEn, Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "TrainName", SqlDbType.VarChar);
            MyDB.AddParameters(nameEn, "TrainEnName", SqlDbType.VarChar);
            MyDB.AddParameters(id, "TrainId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_TrainDef set TrainName = @TrainName,TrainEnName=@TrainEnName where TrainId = @TrainId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }
    }
}

